﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    /// <summary>
    /// Lấy chuổi kết nối connectionString trong Web.config
    /// </summary>
    public static String ConnectionString
    {
        get { return WebConfigurationManager.ConnectionStrings["DBKC"].ConnectionString; }
    }

    /// <summary>
    /// Sử dụng cho câu lệnh SELECT
    /// </summary>
    /// <param name="query">Câu lệnh SQL</param>
    /// <param name="d">Tham số của câu lệnh SQL</param>
    /// <returns>Table</returns>
    public static DataTable GetList(String query, IDictionary<String, String> d)
    {
        String connectionString = ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand comd = new SqlCommand();
        comd.Connection = con;
        comd.CommandText = query;
        comd.CommandType = CommandType.StoredProcedure;        
        if (d != null)
        {
            IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
            while (en.MoveNext())
            {
                SqlParameter param = new SqlParameter();
                param.ParameterName = en.Key.ToString();
                param.Value = en.Value;
                comd.Parameters.Add(param);
            }
        }
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = comd;
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        return dt;
    }

    /// <summary>
    /// Sử dụng cho câu lệnh Insert, Update, Delete
    /// </summary>
    /// <param name="query">Câu lệnh SQL</param>
    /// <param name="d">Tham số của câu lệnh SQL</param>
    /// <returns>Số dòng bị ảnh hưởng trong CSDL</returns>
    public static int ExecuteNonQuery(String query, IDictionary<String, String> d)
    {
        String connectionString = ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand comd = new SqlCommand();
        comd.Connection = con;
        comd.CommandText = query;
        comd.CommandType = CommandType.StoredProcedure;
        if (d != null)
        {
            IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
            while (en.MoveNext())
            {
                SqlParameter param = new SqlParameter();
                param.ParameterName = en.Key.ToString();
                param.Value = en.Value;
                comd.Parameters.Add(param);
            }
        }
        con.Open();
        int effectRecord = 0;
        effectRecord = comd.ExecuteNonQuery();
        con.Close();
        return effectRecord;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="query">Câu lệnh SQL</param>
    /// <param name="d">Tham số câu lệnh SQL</param>
    /// <returns>Giá trị đơn</returns>
    public static object ExecuteScalar(String query, IDictionary<String, String> d)
    {
        String connectionString = ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand comd = new SqlCommand();
        comd.Connection = con;
        comd.CommandText = query;
        comd.CommandType = CommandType.StoredProcedure;
        if (d != null)
        {
            IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
            while (en.MoveNext())
            {
                SqlParameter param = new SqlParameter();
                param.ParameterName = en.Key.ToString();
                param.Value = en.Value;
                comd.Parameters.Add(param);
            }
        }
        con.Open();
        object result = comd.ExecuteScalar();
        con.Close();
        return result;
    }
    

}
